#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

sql="
insert overwrite table jtp_app_ads.ads_traffic_page_view_report
select '$data_date',
       1,
       province,
       city,
       sum(session_pv),
       count(distinct device_id),
       count(distinct session_id),
       count(distinct session_id) / count(distinct device_id),
       sum(session_during_time),
       sum(session_during_time) / count(distinct session_id),
       sum(if(session_pv = 1,1,0)),
       sum(if(session_pv = 1,1,0)) / count(distinct session_id)
from jtp_app_dws.dws_event_log_session_agg
where dt = '$data_date'
group by province,city
union
select '$data_date',
       7,
       province,
       city,
       sum(session_pv),
       count(distinct device_id),
       count(distinct session_id),
       count(distinct session_id) / count(distinct device_id),
       sum(session_during_time),
       sum(session_during_time) / count(distinct session_id),
       sum(if(session_pv = 1,1,0)),
       sum(if(session_pv = 1,1,0)) / count(distinct session_id)
from jtp_app_dws.dws_event_log_session_agg
where dt > date_sub('$data_date',7)
group by province,city
union
select '$data_date',
       30,
       province,
       city,
       sum(session_pv),
       count(distinct device_id),
       count(distinct session_id),
       count(distinct session_id) / count(distinct device_id),
       sum(session_during_time),
       sum(session_during_time) / count(distinct session_id),
       sum(if(session_pv = 1,1,0)),
       sum(if(session_pv = 1,1,0)) / count(distinct session_id)
from jtp_app_dws.dws_event_log_session_agg
where dt > date_sub('$data_date',30)
group by province,city;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${sql}"